package com.maimang.platform.common.utils.excel;

import com.maimang.platform.base.Result;
import com.maimang.platform.common.config.Global;
import com.maimang.platform.common.utils.DateUtils;
import com.maimang.platform.exception.BusinessException;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ooxml.POIXMLProperties;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author Rocky
 * @version 1.0.0
 * @classname ExcelTool
 * @description <br>Excel处理工具</br>
 * excel处理工具
 * 概念-> 表头数据:报表的表头
 * 行内数据:表头以下的数据
 * 功能:动态生成单级，多级Excel表头
 * 备注：tree型结构数据的root节点的id必须为零（0）
 * @date 2019-10-22 10:23
 * @email jintaozhao@qq.com
 */
public class ExcelTool<T> {

	private static final Logger log = LoggerFactory.getLogger(ExcelTool.class);

	/**
	 * excel 对象
	 */
	private XSSFWorkbook workbook;

	/**
	 * 表格标题
	 */
	private Map<String, String> sheetNameMap;
	/**
	 * 单元格宽度
	 */
	private int colWidth = 20;
	/**
	 * 单元格行高度
	 */
	private int rowHeight = 20;
	/**
	 * 表头样式
	 */
	private XSSFCellStyle styleHead;
	/**
	 * 主体样式
	 */
	private XSSFCellStyle styleBody;

	/**
	 * Excel 最大行数 2007版以后	 2^20=1048576行
	 */
	private int maxColumn = 1048576;
	/**
	 * Excel 最大列数 2007版以后2^14=16384列
	 */
	private int maxRow = 1048576;


	/**
	 * 日期格式化,默认yyyy-MM-dd HH:mm:ss
	 */
	private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	/**
	 * 无参数 初始化 对象
	 */
	public ExcelTool() {
		this.sheetNameMap = new LinkedHashMap<>();
		this.sheetNameMap.put("new", "sheet1");
		this.workbook = new XSSFWorkbook();
		initWorkbook();
		init(0);
	}

	private void initWorkbook() {
		// 设置核心属性
		POIXMLProperties.CoreProperties props = this.workbook.getProperties().getCoreProperties();
		props.setCreator("赵锦涛");
		props.setTitle("导出报表");
		props.setDescription("路漫漫其修远兮，吾将上下而求索");
		props.setCategory("platform系统导出");
		props.setCreated(DateUtils.getTime());
		props.setDescription(DateUtils.getTime() + "生成文档");
		// 设置扩展属性
		POIXMLProperties.ExtendedProperties extProps = this.workbook.getProperties().getExtendedProperties();
		this.workbook.getProperties().getExtendedProperties().getCompany();
		// 设置自定义属性
		POIXMLProperties.CustomProperties customProps = this.workbook.getProperties().getCustomProperties();
		customProps.addProperty("公司名称", "西安微聚");
	}

	/**
	 * 有参数 初始化 对象
	 *
	 * @param sheetName  sheet标题
	 * @param colWidth
	 * @param rowHeight
	 * @param dateFormat
	 */
	public ExcelTool(String sheetName, int colWidth, int rowHeight, String dateFormat) {
		this.colWidth = colWidth;
		this.rowHeight = rowHeight;
		this.sheetNameMap = new LinkedHashMap<>();
		this.sheetNameMap.put("new", sheetName);
		this.workbook = new XSSFWorkbook();
		this.sdf = new SimpleDateFormat(dateFormat);
		init(0);
	}

	public ExcelTool(String sheetName, int colWidth, int rowHeight) {
		this.colWidth = colWidth;
		this.rowHeight = rowHeight;
		sheetNameMap = new LinkedHashMap<>();
		sheetNameMap.put("new", sheetName);
		this.workbook = new XSSFWorkbook();
		init(0);
	}

	public ExcelTool(Map<String, String> sheetNameMap, int colWidth, int rowHeight) {
		this.colWidth = colWidth;
		this.rowHeight = rowHeight;
		this.sheetNameMap = sheetNameMap;
		this.workbook = new XSSFWorkbook();
		this.initWorkbook();
		this.init(0);
	}

	public ExcelTool(String sheetName, int colWidth, int rowHeight, int flag) {
		this.colWidth = colWidth;
		this.rowHeight = rowHeight;
		this.sheetNameMap = new LinkedHashMap<>();
		this.sheetNameMap.put("new", sheetName);
		this.workbook = new XSSFWorkbook();
		this.initWorkbook();
		this.init(flag);
	}

	public ExcelTool(String sheetName) {
		this.sheetNameMap = new LinkedHashMap<>();
		this.sheetNameMap.put("new", sheetName);
		this.workbook = new XSSFWorkbook();
		this.initWorkbook();
		this.init(0);
	}

	public ExcelTool(Map<String, String> sheetNameMap) {
		this.sheetNameMap = sheetNameMap;
		this.workbook = new XSSFWorkbook();
		this.initWorkbook();
		this.init(0);
	}

	/**
	 * 设置基础的参数
	 *
	 * @param list
	 */
	public static void setParm(List<Column> list, String rootid) {
		int row = 0;//excel第几行
		int rLen = 0; //excel 跨多少行
		int totalRow = TreeTool.getMaxStep(list);
		int totalCol = TreeTool.getDownChilren(list, rootid);
		for (int i = 0; i < list.size(); i++) {
			Column poit = list.get(i);
			int tree_step = TreeTool.getTreeStep(list, poit.getPid(), 0);//往上遍历tree
			poit.setTree_step(tree_step);
			poit.setRow(tree_step);//设置第几行
			//判断是否有节点
			boolean hasCh = TreeTool.hasChild(list, poit);
			poit.setHasChilren(hasCh);
			if (hasCh) {
				poit.setrLen(0);//设置跨多少行
			} else {
				if (tree_step < totalRow) {
					rLen = totalRow - tree_step;
				}
				poit.setrLen(rLen);
			}
//            boolean flag=false;//控制只有root 节点才有总的行数信息
//            if(rootid == null && rootid == poit.getId() )flag = true;
//            if(rootid != null && rootid.equals(poit.getId()))flag = true;
//            if(flag){
//
//            }
			poit.setTotalRow(totalRow);
			poit.setTotalCol(totalCol);
		}
	}

	/**
	 * 设置基础的参数
	 *
	 * @param list     所有list数据，一条一条
	 * @param treeList 转成tree结构的list
	 */
	public static void setColNum(List<Column> list, List<Column> treeList, List<Column> flist) {
//        int col = pcIndex;//excel第几列
//        int cLen ;//xcel跨多少列
		List<Column> new_list = new ArrayList<>();//新的遍历list
		for (int i = 0; i < treeList.size(); i++) {
			Column poit = treeList.get(i);
//            String temp_id = TreeTool.getStepFid(list,poit.getId() ,1);
			int col = TreeTool.getFCol(list, poit.getPid()).getCol();
			int brotherCol = TreeTool.getBrotherChilNum(list, poit);
			poit.setCol(col + brotherCol);
			int cLen = TreeTool.getDownChilren(list, poit.getId());
			if (cLen <= 1) cLen = 0;
//            else  cLen--;
			poit.setcLen(cLen);//设置跨多少列
			if (poit.getColumns().size() > 0) {
				new_list.addAll(poit.getColumns());
			}
		}
		if (new_list.size() > 0) {
			setColNum(list, new_list, flist);
		}
	}

	/**
	 * 根据XSSFCell类型设置数据
	 *
	 * @param cell 单元格
	 * @return
	 */
	public static String getCellFormatValue(Cell cell) {
		String cellvalue = "";
		if (cell != null) {
			switch (cell.getCellType()) { // 判断当前Cell的Type

				case NUMERIC:  // 如果当前Cell的Type为NUMERIC
				case FORMULA: {
					// 判断当前的cell是否为Date
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						Date date = cell.getDateCellValue();
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
						cellvalue = sdf.format(date);
					} else { // 如果是纯数字
						cellvalue = String.valueOf(cell.getNumericCellValue());
					}
					break;
				}
				case STRING:  // 如果当前Cell的Type为STRIN
					// 取得当前的Cell字符串
					cellvalue = cell.getRichStringCellValue().getString();
					break;
				default:  // 默认的Cell值
					cellvalue = "";
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;
	}

	/**
	 * 描述：根据文件后缀，自适应上传文件的版本
	 *
	 * @param inStr,fileName
	 * @return
	 * @throws Exception
	 */
	public static Workbook getWorkbookType(InputStream inStr, String fileName) throws Exception {
		Workbook wb = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if (".xls".equals(fileType)) {
			wb = new XSSFWorkbook(inStr);  //2003-
		} else if (".xlsx".equals(fileType)) {
			wb = new XSSFWorkbook(inStr);  //2007+
		} else {
			throw new Exception("导入格式错误");
		}
		return wb;
	}

	/**
	 * 获取单元格数据内容为字符串类型的数据
	 *
	 * @param cell Excel单元格
	 * @return String 单元格数据内容
	 */
	public static String getStringCellValue(Cell cell) {
		String strCell = "";
		if (cell == null) {
			return "";
		}
		switch (cell.getCellType()) {
			case STRING:
				strCell = cell.getStringCellValue().trim();
				break;
			case NUMERIC:
				strCell = String.valueOf(cell.getNumericCellValue()).trim();
				break;
			case BOOLEAN:
				strCell = String.valueOf(cell.getBooleanCellValue()).trim();
				break;
			case BLANK:
				strCell = "";
				break;
			default:
				strCell = "";
				break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}
		return strCell;
	}

	/**
	 * 判断整行是否为空
	 *
	 * @param row       excel得行对象
	 * @param maxColumn 有效值得最大列数
	 */
	private static boolean checkRowNull(Row row, int maxColumn) {
		int num = 0;
		for (int j = 0; j < maxColumn; j++) {
			Cell cell = row.getCell(j);
			if (cell == null || cell.equals("") || cell.getCellType() == CellType.BLANK) {
				num++;
			}
		}
		if (maxColumn == num) return true;
		return false;
	}

	/**
	 * 将多个excel工作表列表合并成一个新的
	 *
	 * @param workbookList 多个excel工作表列表
	 * @return
	 */
	public static XSSFWorkbook mergeIntoSingle(List<XSSFWorkbook> workbookList) {
		XSSFWorkbook newWorkbook = new XSSFWorkbook();
		for (XSSFWorkbook fromExcel : workbookList) {   // 遍历每个源excel文件，TmpList为源文件的名称集合
			int length = fromExcel.getNumberOfSheets();
			if (length <= 1) {       //长度为1时
				XSSFSheet oldSheet = fromExcel.getSheetAt(0);
				XSSFSheet newSheet = newWorkbook.createSheet(oldSheet.getSheetName());
				copySheet(newWorkbook, oldSheet, newSheet);
			} else {
				for (int i = 0; i < length; i++) {// 遍历每个sheet
					XSSFSheet oldSheet = fromExcel.getSheetAt(i);
					XSSFSheet newSheet = newWorkbook.createSheet(oldSheet.getSheetName());
					copySheet(newWorkbook, oldSheet, newSheet);
				}
			}
		}
		return newWorkbook;
	}

	/**
	 * Sheet复制
	 *
	 * @param wb
	 * @param fromSheet
	 * @param toSheet
	 */
	public static void copySheet(XSSFWorkbook wb, XSSFSheet fromSheet, XSSFSheet toSheet) {
		mergeSheetAllRegion(fromSheet, toSheet);
		// 设置列宽
		int length = fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum();
		for (int i = 0; i <= length; i++) {
			toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
		}
		for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) {
			XSSFRow oldRow = (XSSFRow) rowIt.next();
			XSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
			copyRow(wb, oldRow, newRow);
		}
	}

	/**
	 * 行复制功能
	 *
	 * @param wb
	 * @param oldRow
	 * @param toRow
	 */
	public static void copyRow(XSSFWorkbook wb, XSSFRow oldRow, XSSFRow toRow) {
		toRow.setHeight(oldRow.getHeight());
		for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext(); ) {
			XSSFCell tmpCell = (XSSFCell) cellIt.next();
			XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
			copyCell(wb, tmpCell, newCell);
		}
	}

	/**
	 * 合并单元格
	 *
	 * @param fromSheet
	 * @param toSheet
	 */
	public static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet toSheet) {
		int num = fromSheet.getNumMergedRegions();
		CellRangeAddress cellR = null;
		for (int i = 0; i < num; i++) {
			cellR = fromSheet.getMergedRegion(i);
			toSheet.addMergedRegion(cellR);
		}
	}

	/**
	 * 复制单元格
	 *
	 * @param wb
	 * @param fromCell
	 * @param toCell
	 */
	public static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {
		XSSFCellStyle newstyle = wb.createCellStyle();
		copyCellStyle(fromCell.getCellStyle(), newstyle);
		//  toCell.setEncoding(fromCell.getStringCelllValue());
		// 样式
		toCell.setCellStyle(newstyle);
		if (fromCell.getCellComment() != null) {
			toCell.setCellComment(fromCell.getCellComment());
		}
		// 不同数据类型处理
		CellType fromCellType = fromCell.getCellType();
		toCell.setCellType(fromCellType);
		if (fromCellType == CellType.NUMERIC) {
			if (HSSFDateUtil.isCellDateFormatted(fromCell)) {
				toCell.setCellValue(fromCell.getDateCellValue());
			} else {
				toCell.setCellValue(fromCell.getNumericCellValue());
			}
		} else if (fromCellType == CellType.STRING) {
			toCell.setCellValue(fromCell.getRichStringCellValue());
		} else if (fromCellType == CellType.BLANK) {
			// nothing21
		} else if (fromCellType == CellType.BOOLEAN) {
			toCell.setCellValue(fromCell.getBooleanCellValue());
		} else if (fromCellType == CellType.ERROR) {
			toCell.setCellErrorValue(fromCell.getErrorCellValue());
		} else if (fromCellType == CellType.FORMULA) {
			toCell.setCellFormula(fromCell.getCellFormula());
		} else { // nothing29
		}
	}

	/**
	 * 复制单元格样式
	 *
	 * @param fromStyle
	 * @param toStyle
	 */
	public static void copyCellStyle(XSSFCellStyle fromStyle, XSSFCellStyle toStyle) {
		toStyle.cloneStyleFrom(fromStyle);
	}

	/**
	 * 对list数据源将其里面的数据导入到excel表单
	 *
	 * @param fileName 文件名称
	 * @return 结果
	 */
	public Result exportExcel(String fileName,
	                          Map<String, List<Column>> columnsMap,
	                          Map<String, List<T>> datasMap,
	                          boolean flag,
	                          boolean rowFlag) {
		OutputStream out = null;
		try {
			splitSheets(columnsMap, datasMap, flag, rowFlag);
			String filename = encodingFilename(fileName);
			out = new FileOutputStream(getAbsoluteFile(filename));
			workbook.write(out);
			return Result.SUCCESS(200, filename);
		} catch (Exception e) {
			log.error("导出Excel异常{}", e.getMessage());
			e.printStackTrace();
			throw new BusinessException("导出Excel失败，请联系网站管理员！");
		} finally {
			if (workbook != null) {
				try {
					workbook.close();
				} catch (IOException e1) {
					e1.printStackTrace();
				}
			}
			if (out != null) {
				try {
					out.close();
				} catch (IOException e1) {
					e1.printStackTrace();
				}
			}
		}
	}

	/**
	 * 获取下载路径
	 *
	 * @param filename 文件名称
	 */
	public String getAbsoluteFile(String filename) {
		String downloadPath = Global.getDownloadPath() + filename;
		File desc = new File(downloadPath);
		if (!desc.getParentFile().exists()) {
			desc.getParentFile().mkdirs();
		}
		return downloadPath;
	}

	/**
	 * 编码文件名
	 */
	public String encodingFilename(String filename) {
		filename = filename + "_" + DateUtils.getDate2() + ".xlsx";
		return filename;
	}

	/**
	 * ExcelTool 属性 get、set 方法 开始
	 */
	public int getColWidth() {
		return colWidth;
	}

	public void setColWidth(int colWidth) {
		this.colWidth = colWidth;
	}

	public int getRowHeight() {
		return rowHeight;
	}

	public void setRowHeight(int rowHeight) {
		this.rowHeight = rowHeight;
	}

	public XSSFWorkbook getWorkbook() {
		return this.workbook;
	}

	public void setWorkbook(XSSFWorkbook workbook) {
		this.workbook = workbook;
	}

	public XSSFCellStyle getStyleHead() {
		return styleHead;
	}

	public void setStyleHead(XSSFCellStyle styleHead) {
		this.styleHead = styleHead;
	}

	public XSSFCellStyle getStyleBody() {
		return styleBody;
	}

	public void setStyleBody(XSSFCellStyle styleBody) {
		this.styleBody = styleBody;
	}

	/**
	 * ExcelTool 属性 get、set 方法 结束
	 */
	//内部统一调用的样式初始化
	private void init(int styleFlag) {
		this.styleHead = this.workbook.createCellStyle();
		this.styleHead.setAlignment(HorizontalAlignment.LEFT);// 左右居中
		this.styleHead.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
		this.styleHead.setRightBorderColor(IndexedColors.BLACK.index);
		this.styleHead.setBottomBorderColor(IndexedColors.BLACK.index);
		switch (styleFlag) {
			case 1:
				this.styleBody = this.workbook.createCellStyle();
				this.styleBody.setAlignment(HorizontalAlignment.LEFT);// 左右居中ALIGN_CENTER
				this.styleBody.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
				this.styleBody.setRightBorderColor(IndexedColors.BLACK.index);
				this.styleBody.setBottomBorderColor(IndexedColors.BLACK.index);
				this.styleBody.setBorderRight(BorderStyle.THIN);// 边框的大小
				this.styleBody.setBorderBottom(BorderStyle.THIN);// 边框的大小
				break;
			default:
				this.styleBody = this.workbook.createCellStyle();
				this.styleBody.setAlignment(HorizontalAlignment.CENTER);// 左右居中ALIGN_CENTER
				this.styleBody.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
				this.styleBody.setRightBorderColor(IndexedColors.BLACK.index);
				this.styleBody.setBottomBorderColor(IndexedColors.BLACK.index);
				this.styleBody.setBorderRight(BorderStyle.THIN);// 边框的大小
				this.styleBody.setBorderBottom(BorderStyle.THIN);// 边框的大小
				break;
		}
	}

	/**
	 * 导出表格 无返回
	 *
	 * @param columnsMap 表头数据
	 * @param datasMap   行内数据
	 * @param filePath   保存路径
	 * @param flag
	 * @param rowFlag
	 * @throws Exception
	 */
	public void exportExcel(Map<String, List<Column>> columnsMap,
	                        Map<String, List<T>> datasMap,
	                        String filePath, boolean flag, boolean rowFlag) throws Exception {
		splitSheets(columnsMap, datasMap, flag, rowFlag);
		save(this.workbook, filePath);
	}

	/**
	 * 整理导出表格 无返回
	 * 需要确保columnsMap和datasMap数据列表长度一致,key相同
	 *
	 * @param columnsMap 表头数据
	 * @param datasMap   行内数据
	 * @param flag
	 * @param rowFlag
	 * @throws Exception
	 */
	private void splitSheets(Map<String, List<Column>> columnsMap,
	                         Map<String, List<T>> datasMap,
	                         boolean flag, boolean rowFlag) throws Exception {
		for (Map.Entry<String, List<Column>> dataMap : columnsMap.entrySet()) {
			String key = dataMap.getKey();
			List<Column> columnList = dataMap.getValue();
			String sheetName = sheetNameMap.containsKey(key) ? sheetNameMap.get(key) : key;
			List<T> dataList = datasMap.containsKey(key) ? datasMap.get(key) : new ArrayList<>();
			splitDataToSheets(dataList, columnList, sheetName, flag, rowFlag);
		}
	}

	/**
	 * 返回workbook
	 *
	 * @param columnsMap 表头数据
	 * @param datasMap   行内数据
	 * @param flag       是否写入行内数据
	 * @return
	 * @throws Exception
	 */
	public XSSFWorkbook exportWorkbook(Map<String, List<Column>> columnsMap,
	                                   Map<String, List<T>> datasMap,
	                                   boolean flag) throws Exception {
		splitSheets(columnsMap, datasMap, flag, false);
		return this.workbook;
	}

	/**
	 * 导出表格 有返回值
	 *
	 * @param columnsMap 表头数据列表，每个sheet页表头都是list
	 * @param datasMap   行内数据
	 * @param flag       只输出表头数据
	 * @param rowFlag
	 * @return
	 * @throws Exception
	 */
	public InputStream exportExcel(Map<String, List<Column>> columnsMap,
	                               Map<String, List<T>> datasMap,
	                               boolean flag, boolean rowFlag) throws Exception {
		splitSheets(columnsMap, datasMap, flag, rowFlag);
		return save(this.workbook);
	}

	/**
	 * 导出Excel,适用于web导出excel
	 *
	 * @param sheet   excel
	 * @param data    行内数据
	 * @param columns 表头数据
	 * @param flag    只输出表头数据
	 * @param rowFlag 输出展示数据的结构(表头下面行的数据)
	 * @throws Exception
	 */
	private void writeSheet(XSSFSheet sheet, List<T> data, List<Column> columns, boolean flag, boolean rowFlag) throws Exception {
		sheet.setDefaultColumnWidth(colWidth);
		sheet.setDefaultRowHeightInPoints(rowHeight);
		sheet = createHead(sheet, columns.get(0).getTotalRow(), columns.get(0).getTotalCol());
		createHead(columns, sheet, 0);
		//控制是否 bug修复：每次写入行数据时，总是漏第一个条数据 rowIndex 错误
		if (flag)
			writeSheetContent(columns, data, sheet, columns.get(0).getTotalRow() + 1, rowFlag);
	}

	/**
	 * 拆分sheet，因为每个sheet不能超过1048576，否则会报异常
	 *
	 * @param data      行内数据
	 * @param columns   表头数据
	 * @param sheetName sheet 名称
	 * @param flag      只输出表头数据
	 * @param rowFlag   输出展示数据的结构(表头下面行的数据)
	 * @throws Exception
	 */
	private void splitDataToSheets(List<T> data,
	                               List<Column> columns,
	                               String sheetName,
	                               boolean flag,
	                               boolean rowFlag) throws Exception {
		int dataCount = data.size();

		int pieces = dataCount / maxRow;
		if (pieces >= 1) {
			for (int i = 1; i <= pieces; i++) {
				XSSFSheet sheet = this.workbook.createSheet(sheetName + i);
				List<T> subList = data.subList((i - 1) * maxRow, i * maxRow);
				writeSheet(sheet, subList, columns, flag, rowFlag);
			}
			XSSFSheet sheet = this.workbook.createSheet(sheetName + (pieces + 1));
			writeSheet(sheet, data.subList(pieces * maxRow, dataCount), columns, flag, rowFlag);
		} else {
			XSSFSheet sheet = this.workbook.createSheet(sheetName);
			writeSheet(sheet, data, columns, flag, rowFlag);
		}
	}

	/**
	 * 把数据写入到单元格
	 *
	 * @param columns 表头数据
	 * @param datas   行内数据
	 * @param sheet   工作表（excel分页）
	 * @throws Exception void
	 */
	private void writeSheetContent(List<Column> columns, List<T> datas, XSSFSheet sheet, int rowIndex, boolean rowFlag) throws Exception {
		XSSFRow row = null;
		List<Column> listCol = new ArrayList<>();
		rowFlag = false;
		if (rowFlag) {//暂时没有用 后面扩展用
			for (int i = 0, index = rowIndex; i < datas.size(); i++, index++) {
				row = sheet.createRow(index);//创建行
				for (int j = 0; j < columns.size(); j++) {
					createColl(row, j, columns.get(j).getFieldName(), datas.get(i));
				}
			}
		} else {
			getColumnList(columns, listCol);
			for (int i = 0, index = rowIndex; i < datas.size(); i++, index++) {
				row = sheet.createRow(index);//创建行
				for (int j = 0; j < listCol.size(); j++) {
					Column c = listCol.get(j);
					createCol(row, c, datas.get(i));
				}

			}
		}
	}

	/**
	 * 根据list 来创建单元格 暂时没有用
	 *
	 * @param row
	 * @param j
	 * @param finame
	 * @param t
	 */
	private void createColl(XSSFRow row, int j, String finame, T t) {
		XSSFCell cell = row.createCell(j);  //创建单元格
		cell.setCellStyle(this.styleBody); //设置单元格样式
		String text = "";
		if (t instanceof List) {
			List<Map> temp = (List<Map>) t;
			if (j >= temp.size()) return;
			text = String.valueOf(temp.get(j).get(finame) == null ? "" : temp.get(j).get(finame));
		}
		XSSFRichTextString richString = new XSSFRichTextString(text);
		cell.setCellValue(richString);
	}

	/**
	 * 把column的columnList整理成一个list<column> 过滤表头的脏数据
	 *
	 * @param list    表头数据
	 * @param listCol 返回新的list
	 * @return List<column>
	 */
	private void getColumnList(List<Column> list, List<Column> listCol) {
		for (int i = 0; i < list.size(); i++) {
			if (list.get(i).getFieldName() != null) {
				listCol.add(list.get(i));
			}
			List<Column> listChilren = list.get(i).getColumns();
			if (listChilren.size() > 0) {
				getColumnList(listChilren, listCol);
			}
		}
	}
//========上部分是导出excel的使用（生成excel），下部分是解析excel，由于excel导入============

	/**
	 * 保存Excel到InputStream，此方法适合web导出excel
	 *
	 * @param workbook
	 * @return
	 */
	private InputStream save(XSSFWorkbook workbook) {
		ByteArrayOutputStream bos = new ByteArrayOutputStream();
		try {
			workbook.write(bos);
			InputStream bis = new ByteArrayInputStream(bos.toByteArray());
			return bis;
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

	/**
	 * 保存excel到本机指定的路径
	 *
	 * @param workbook
	 * @param filePath
	 * @throws IOException
	 */
	private void save(XSSFWorkbook workbook, String filePath) {
		File file = new File(filePath);
		if (!file.getParentFile().exists()) {
			file.getParentFile().mkdirs();
		}
		FileOutputStream fOut = null;
		try {
			fOut = new FileOutputStream(file);
			workbook.write(fOut);
			fOut.flush();
		} catch (Exception e) {
			e.printStackTrace();
		}
		try {
			if (null != fOut) fOut.close();
		} catch (Exception e1) {
		}
	}

	/**
	 * 创建行
	 *
	 * @param row         Excel对应的行
	 * @param tpamscolumn 当前单元格属性
	 * @param v
	 * @param j
	 * @return
	 * @throws Exception
	 */
	public int createRowVal(XSSFRow row, Column tpamscolumn, T v, int j) throws Exception {
		//遍历标题
		if (tpamscolumn.getColumns() != null && tpamscolumn.getColumns().size() > 0) {
			for (int i = 0; i < tpamscolumn.getColumns().size(); i++) {
				createRowVal(row, tpamscolumn.getColumns().get(i), v, j);
			}
		} else {
			createCol(row, tpamscolumn, v);
		}
		return j;

	}

	/**
	 * 创建单元格
	 *
	 * @param row         Excel对应的行
	 * @param tpamscolumn 当前单元格对象
	 * @param v
	 * @throws Exception
	 */
	public void createCol(XSSFRow row, Column tpamscolumn, T v) throws Exception {
		XSSFCell cell = row.createCell(tpamscolumn.getCol());  //创建单元格
		cell.setCellStyle(this.styleBody); //设置单元格样式
		final Object[] value = {null};
		if (v instanceof Map) {
			Map m = (Map) v;
			m.forEach((k, val) -> {
				if (k.equals(tpamscolumn.getFieldName()) && !tpamscolumn.isHasChilren()) {
					value[0] = val;
				}
			});
		} else {
			Class<?> cls = v.getClass();// 拿到该类
			Field[] fields = cls.getDeclaredFields();// 获取实体类的所有属性，返回Field数组
			for (int i = 0; i < fields.length; i++) {
				Field f = fields[i];
				f.setAccessible(true); // 设置些属性是可以访问的
				if (tpamscolumn.getFieldName().equals(f.getName()) && !tpamscolumn.isHasChilren())// && !tpamscolumn.isHasChilren()
					value[0] = f.get(v);
				if (value[0] instanceof Date)
					value[0] = parseDate((Date) value[0]);
			}
		}
		if (value[0] != null) {
			XSSFRichTextString richString = new XSSFRichTextString(value[0].toString());
			cell.setCellValue(richString);
		}

	}

	/**
	 * 时间转换
	 *
	 * @param date
	 * @return String
	 */
	private String parseDate(Date date) {
		String dateStr = "";
		try {
			dateStr = this.sdf.format(date);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dateStr;
	}

	/**
	 * 根据数据的行数和列数，在excel创建单元格cell
	 *
	 * @param sheetCo excel分页
	 * @param r       excel 行数
	 * @param c       excel 列数
	 * @return
	 */
	public XSSFSheet createHead(XSSFSheet sheetCo, int r, int c) {
		for (int i = 0; i < r; i++) {
			XSSFRow row = sheetCo.createRow(i);
			for (int j = 0; j < c; j++) {
				XSSFCell cell = row.createCell(j);
			}
		}
		return sheetCo;
	}

	/**
	 * 使用递归 在excel写入表头数据 支持单级，多级表头的创建
	 *
	 * @param columns  表头数据
	 * @param sheetCo  哪个分页
	 * @param rowIndex 当前Excel的第几行
	 */
	public void createHead(List<Column> columns, XSSFSheet sheetCo, int rowIndex) {
		XSSFRow row = sheetCo.getRow(rowIndex);
//        if(row == null)row = sheetCo.createRow(rowIndex);
		int len = columns.size();//当前行 有多少列
		for (int i = 0; i < len; i++) {//i是headers的索引，n是Excel的索引 多级表头
			Column tpamscolumn = columns.get(i);
			//创建这一行的第几列单元格
			int r = tpamscolumn.getRow();
			int rLen = tpamscolumn.getrLen();
			int c = tpamscolumn.getCol();
			int cLen = tpamscolumn.getcLen();
			int endR = r + rLen;
			int endC = c + cLen;
			if (endC > c) {
				endC--;
			}
			XSSFCell cell = row.getCell(c);
//            if( null == cell)cell = row.createCell(c);

			XSSFRichTextString text = new XSSFRichTextString(tpamscolumn.getContent());
			cell.setCellStyle(this.styleHead); //设置表头样式
			cell.setCellValue(text);
			// 合并单元格
			CellRangeAddress cra = new CellRangeAddress(r, endR, c, endC);
			if (cra.getNumberOfCells() > 1) {
				sheetCo.addMergedRegion(cra);
			}

			// 使用RegionUtil类为合并后的单元格添加边框
			RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheetCo); // 下边框
			RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheetCo); // 左边框
			RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheetCo); // 有边框

			if (tpamscolumn.isHasChilren()) {
				rowIndex = r + 1;
				createHead(tpamscolumn.getColumns(), sheetCo, rowIndex);
			}
		}
	}

	/**
	 * 转换成column对象
	 * 支持List<T>的数据结构:map String ，只能是单级的数据
	 *
	 * @param list 需要转换的数据
	 * @return
	 */
	public List<Column> columnTransformer(List<T> list) {
		List<Column> lc = new ArrayList<>();
		if (list.get(0) instanceof Map) {
			final int[] i = {1};
			for (Map<String, String> m : (List<Map<String, String>>) list) {
				m.forEach((k, val) -> {
					Column tpamscolumn = new Column();
					tpamscolumn.setId(String.valueOf(i[0]));
					tpamscolumn.setPid("0");
					tpamscolumn.setContent(k);
					tpamscolumn.setFieldName(val);
					lc.add(tpamscolumn);
					i[0]++;
				});
			}
		} else {
			int i = 1;
			for (String s : (List<String>) list) {
				Column tpamscolumn = new Column();
				tpamscolumn.setId(String.valueOf(i));
				tpamscolumn.setPid("0");
				tpamscolumn.setContent(s);
				tpamscolumn.setFieldName(null);
				lc.add(tpamscolumn);
				i++;
			}
		}
		setParm(lc, "0");//处理一下
		List<Column> s = TreeTool.buildByRecursive(lc, "0");
		setColNum(lc, s, s);
		return s;
	}

	/**
	 * 转换成column对象list 其中元素均返回tree数据结构
	 * 支持：List<map>、某个具体对象（entity）数据的转换
	 *
	 * @param listMap   需要转换的数据
	 * @param id        当前节点id 字段的名称  主键
	 * @param pid       父节点id 字段的名称
	 * @param content   填写表头单元格内容的 字段名称
	 * @param fieldName 填写行内数据对的 字段名称
	 * @param rootid    rootid的值
	 * @return
	 * @throws Exception
	 */
	public Map<String, List<Column>> columnsTransformer(Map<String, List<T>> listMap, String id, String pid, String content, String fieldName, String rootid) throws Exception {
		Map<String, List<Column>> columnsMap = new LinkedHashMap<>();

		for (Map.Entry<String, List<T>> entry : listMap.entrySet()) {
			String key = entry.getKey();
			List<T> titleEntityList = entry.getValue();
			List<Column> columnList = this.columnTransformer(titleEntityList, id, pid, content, fieldName, rootid);
			columnsMap.put(key, columnList);
		}

		return columnsMap;
	}

	/**
	 * 转换成column对象 返回tree数据结构
	 * 支持：List<map>、某个具体对象（entity）数据的转换
	 *
	 * @param list      需要转换的数据
	 * @param id        当前节点id 字段的名称  主键
	 * @param pid       父节点id 字段的名称
	 * @param content   填写表头单元格内容的 字段名称
	 * @param fieldName 填写行内数据对的 字段名称
	 * @param rootid    rootid的值
	 * @return
	 * @throws Exception
	 */
	public List<Column> columnTransformer(List<T> list, String id, String pid, String content, String fieldName, String rootid) throws Exception {
		List<Column> lc = new ArrayList<>();
		if (list.get(0) instanceof Map) {
			for (Map m : (List<Map>) list) {
				Column tpamscolumn = new Column();
				m.forEach((k, val) -> {//java8 以上的遍历方式
					if (id.equals(k))
						tpamscolumn.setId(String.valueOf(val));
					if (pid.equals(k)) tpamscolumn.setPid((String) val);
					if (content.equals(k)) tpamscolumn.setContent((String) val);
					if (fieldName.equals(k) && fieldName != null) tpamscolumn.setFieldName((String) val);
				});
				lc.add(tpamscolumn);
			}
		} else {
			for (T t : list) {//反射
				Column tpamscolumn = new Column();
				Class cls = t.getClass();
				Field[] fs = cls.getDeclaredFields();
				for (int i = 0; i < fs.length; i++) {
					Field f = fs[i];
					f.setAccessible(true); // 设置些属性是可以访问的
					if (id.equals(f.getName()) && f.get(t) != null)
						tpamscolumn.setId(f.get(t).toString());
					if (pid.equals(f.getName()) && f.get(t) != null)
						tpamscolumn.setPid(f.get(t).toString());
//                    if (pid.equals(f.getName()) && ( f.get(t) == null || "".equals(f.get(t)))) tpamscolumn.setPid("0");
					if (content.equals(f.getName()) && f.get(t) != null)
						tpamscolumn.setContent(f.get(t).toString());
					if (f.get(t) != null && fieldName != null && fieldName.equals(f.getName()))
						tpamscolumn.setFieldName(f.get(t).toString());
				}
				lc.add(tpamscolumn);
			}
		}
		setParm(lc, rootid);//处理一下
		List<Column> s = TreeTool.buildByRecursive(lc, rootid);
		setColNum(lc, s, s);
		return s;
	}

	/**
	 * 判断指定的单元格是否是合并单元格
	 *
	 * @param sheet
	 * @param row    行下标
	 * @param column 列下标
	 * @return
	 */
	public boolean isMergedRegion(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * 获取合并单元格的值
	 *
	 * @param sheet
	 * @param row    行下标
	 * @param column 列下标
	 * @return
	 */
	public String getMergedRegionValue(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();

		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					Row fRow = sheet.getRow(firstRow);
					Cell fCell = fRow.getCell(firstColumn);
					return getStringCellValue(fCell);
				}
			}
		}
		return "";
	}

	/**
	 * 获取excel的值 返回的 List<List<String>>的数据结构
	 *
	 * @param fileUrl  文件路径
	 * @param sheetNum 工作表（第几分页[1,2,3.....]）
	 */
	public List<List<String>> getExcelValues(String fileUrl, int sheetNum) throws Exception {
		List<List<String>> values = new ArrayList<List<String>>();
		File file = new File(fileUrl);
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		// sheet 数量,可以只读取手动指定的sheet页
		int sheetCount = sheetNum - 1; //workbook.getNumberOfSheets();
		//int sheetCount1= workbook.getNumberOfSheets();
		//读取第几个工作表sheet
		Sheet sheet = workbook.getSheetAt(sheetCount);
		//有多少行
		int rowNum = sheet.getLastRowNum();
		for (int i = 1; i <= rowNum; i++) {
			//第i行
			Row row = sheet.getRow(i);
			if (row == null) {
				//过滤空行
				continue;
			}
			List<String> list = new ArrayList<>();
			//用表头去算有多少列，不然从下面的行计算列的话，空的就不算了
			int colCount = sheet.getRow(0).getLastCellNum();
			//第j列://+1是因为最后一列是空 也算进去
			for (int j = 0; j < colCount; j++) {
				Cell cell = row.getCell(j);
				String cellValue;
				boolean isMerge = false;
				if (cell != null) {
					isMerge = isMergedRegion(sheet, i, cell.getColumnIndex());
				}
				//判断是否具有合并单元格
				if (isMerge) {
					cellValue = getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex());
				} else {
					cellValue = getStringCellValue(cell);
				}
				list.add(cellValue);
			}
			values.add(list);
		}
		return values;
	}

	/**
	 * 根据sheet数获取excel的值 返回List<List<Map<String,String>>>的数据结构
	 *
	 * @param fileUrl  文件路径
	 * @param sheetNum 工作表（第几分页[1,2,3.....]）
	 */
	public List<List<Map<String, String>>> getExcelMapVal(String fileUrl, int sheetNum) throws Exception {
		List<List<Map<String, String>>> values = new ArrayList<List<Map<String, String>>>();
		File file = new File(fileUrl);
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		// sheet 数量,可以只读取手动指定的sheet页
		int sheetCount = sheetNum - 1; //workbook.getNumberOfSheets();
		//int sheetCount1= workbook.getNumberOfSheets();
		//读取第几个工作表sheet
		Sheet sheet = workbook.getSheetAt(sheetCount);
		int rowNum = sheet.getLastRowNum();//有多少行
		Row rowTitle = sheet.getRow(0);//第i行
		//用表头去算有多少列，不然从下面的行计算列的话，空的就不算了
		int colCount = sheet.getRow(0).getLastCellNum();
		for (int i = 1; i <= rowNum; i++) {
			Row row = sheet.getRow(i);//第i行
			if (row == null || checkRowNull(row, colCount)) {//过滤空行
				continue;
			}
			List<Map<String, String>> list = new ArrayList<Map<String, String>>();
			//第j列://+1是因为最后一列是空 也算进去
			for (int j = 0; j < colCount; j++) {
				Map<String, String> map = new HashMap<>();
				Cell cell = row.getCell(j);
				Cell cellTitle = rowTitle.getCell(j);
				String cellValue;
				String cellKey = getStringCellValue(cellTitle);
				boolean isMerge = false;
				if (cell != null) {
					isMerge = isMergedRegion(sheet, i, cell.getColumnIndex());
				}
				//判断是否具有合并单元格
				if (isMerge) {
					cellValue = getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex());
				} else {
					cellValue = getStringCellValue(cell);
				}
				map.put(cellKey, cellValue);
				list.add(map);
			}
			values.add(list);
		}
		return values;
	}

	/**
	 * 获取当前excel的工作表sheet总数
	 *
	 * @param fileUrl
	 * @return
	 * @throws Exception
	 */
	public int hasSheetCount(String fileUrl) throws Exception {
		File file = new File(fileUrl);
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		int sheetCount = workbook.getNumberOfSheets();
		return sheetCount;
	}

}